package cn.qianxun.meta.common.clickhouse.util;

import cn.hutool.core.util.ReflectUtil;
import cn.qianxun.meta.common.clickhouse.config.ClickhouseConfig;
import com.alibaba.fastjson.JSONObject;
import org.springframework.stereotype.Component;
import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @Classname ClickHouseUtil
 * @Description TODO
 * @Date 2021/9/8 16:18
 * @Created by fuzhilin
 */
@Component
public class ClickHouseUtil {

    public static int batchSize=1000;
    @Resource
    public ClickhouseConfig clickConfig;
    private static ClickhouseConfig clickhouseConfig;

    @PostConstruct
    public void init(){
        clickhouseConfig = this.clickConfig;
    }
    private static ClickHouseConnection conn;
    public static ClickHouseConnection getConn() {
        if (conn == null){
            ClickHouseProperties properties = new ClickHouseProperties();
            properties.setUser(clickhouseConfig.getUsername());
            properties.setPassword(clickhouseConfig.getPassword());
            properties.setSocketTimeout(clickhouseConfig.getSocketTimeout());
            properties.setConnectionTimeout(clickhouseConfig.getConnectionTimeout());
            ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(clickhouseConfig.getUrl(),properties);
            try {
                conn = clickHouseDataSource.getConnection();
                return conn;
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
         }
        return conn;
    }

    /**
     *
     * @param sql 查询sql,转换成对应的实体
     * @param beanCalss
     * @param <T>
     * @return
     */
    public static <T> List<T>  selectSql(String sql,Class<T> beanCalss){
        ClickHouseConnection connection = getConn();
        try {
            Statement statement = connection.createStatement();
            ResultSet results = statement.executeQuery(sql);
            ResultSetMetaData rsmd = results.getMetaData();
            List<T> list=new ArrayList<>();
            while(results.next()){
                JSONObject row = new JSONObject();
                for(int i = 1;i<=rsmd.getColumnCount();i++){
                    row.put(rsmd.getColumnName(i),results.getString(rsmd.getColumnName(i)));
                }
                list.add(JSONObject.toJavaObject(row,beanCalss));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 进行单个新增的sql方法
     * @param dbtable 插入的表
     * @param params 插入的相关参数
     * @param entity 插入的实体类的对象
     * @param classPath 插入的实体类的的路径，方便获取相关参数，更安全
     * @return
     */
    public boolean  save(String dbtable,String[] params, Object entity, String classPath) {
        try {
            Class cla = Class.forName(classPath);
            if(cla.isInstance(entity)){
                save(dbtable,params,entity);
            }else{
                return  false;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return  false;
        }
        return true;
    }

    /**
     * 新增操作，不需要传递实体类的路径，但是可能会出现类转换异常的问题
     * @param dbtable 插入的表
     * @param params 插入的相关参数
     * @param entity 插入的实体类的对象
     * @return
     */
    public boolean  save(String dbtable,String[] params, Object entity) {
        StringBuffer sql=new StringBuffer();
        if(params!=null){
            //INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
            sql.append("INSERT INTO"+dbtable+" (");
            StringBuffer paramstr=new StringBuffer();
            paramstr.append("(");
            for (int i = 0; i <params.length ; i++) {
                if(i!=params.length-1){
                    sql.append(params[i]+",");
                    paramstr.append(ReflectUtil.getFieldValue(entity, params[i])+",");
                }else{
                    sql.append(params[i]+") VALUES ");
                    paramstr.append(ReflectUtil.getFieldValue(entity, params[i])+")");
                }
            }
            sql.append(paramstr);
            ClickHouseConnection connection = getConn();
            try {
                Statement statement = connection.createStatement();
                boolean results = statement.execute(sql.toString());
                return results;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    /**
     * 新增操作，不需要传递实体类的路径，但是可能会出现类转换异常的问题
     * @param dbtable 插入的表
     * @param entity 插入的实体类的对象
     * @return
     */
    public boolean  save(String dbtable, Object entity) {
        StringBuffer sql=new StringBuffer();
        //INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
        sql.append("INSERT INTO"+dbtable+" (*)");
        StringBuffer paramstr=new StringBuffer();
        paramstr.append("(");
        Field[] fields = ReflectUtil.getFields(entity.getClass());
        for (int i = 0; i <fields.length ; i++) {
            if(i!=fields.length-1){
                paramstr.append(ReflectUtil.getFieldValue(entity, fields[i].getName())+",");
            }else{
                paramstr.append(ReflectUtil.getFieldValue(entity, fields[i].getName())+")");
            }
        }
        sql.append(paramstr);
        ClickHouseConnection connection = getConn();
        try {
            Statement statement = connection.createStatement();
            boolean results = statement.execute(sql.toString());
           return results;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }

    /**
     * 批量插入操作
     * @param dbtable 插入的表
     * @param params 插入的相关参数
     * @param entityList ignore
     * @return ignore
     */
    public boolean saveBatch(String dbtable,String[] params, List<Object> entityList) {
        return  saveBatch(dbtable,params, entityList,batchSize);
    }

    /**
     * 批量插入操作
     * @param dbtable 插入的表
     * @param params 插入的相关参数
     * @param entityList ignore
     * @return ignore
     */
    public boolean saveBatch(String dbtable,String[] params, List<Object> entityList,int batchsize) {

        if(params!=null){
            int size=entityList.size();
            // 计算可以分成多少组
            int num = ( size + batchsize - 1 )/batchsize ;
            for (int i = 0; i < num; i++) {
                // 开始位置
                int fromIndex = i * batchsize;
                // 结束位置
                int toIndex = (i+1) * batchsize < size ? ( i+1 ) * batchsize : size ;
                List<Object>  entityListson=entityList.subList(fromIndex,toIndex);
                StringBuffer sql=new StringBuffer();
                sql.append("INSERT INTO"+dbtable+" (");
                for (int m = 0; m <params.length ; m++) {
                    if(m!=params.length-1){
                        sql.append(params[m]+",");
                    }else{
                        sql.append(params[m]+") VALUES ");
                    }
                }
                StringBuffer paramstr=new StringBuffer();
                for (int n = 0; n <entityListson.size() ; n++) {
                    paramstr.append("(");
                    for (int m = 0; m <params.length ; m++) {
                        if(m!=params.length-1){
                            paramstr.append(ReflectUtil.getFieldValue(entityListson.get(n), params[m])+",");
                        }else{
                            paramstr.append(ReflectUtil.getFieldValue(entityListson.get(n), params[m]));
                        }
                    }
                    if(n!=entityListson.size() -1){
                        paramstr.append("),");
                    }else{
                        paramstr.append(")");
                    }
                }
                sql.append(paramstr);
                ClickHouseConnection connection = getConn();
                try {
                    Statement statement = connection.createStatement();
                    boolean results = statement.execute(sql.toString());
                    return results;
                } catch (SQLException e) {
                    e.printStackTrace();
                    return false;
                }
            }
        }
        return true;
    }

    /**
     * 批量插入操作
     * @param dbtable 插入的表
     * @param entityList ignore
     * @return ignore
     */
    public boolean saveBatch(String dbtable, List<Object> entityList,int batchsize) {
        if(entityList==null){
            return true;
        }
        int size=entityList.size();
        if(size==0){
            return true;
        }
        // 计算可以分成多少组
        int num = ( size + batchsize - 1 )/batchsize ;
        Field[] fields = ReflectUtil.getFields(entityList.get(0).getClass());
        for (int i = 0; i < num; i++) {
            // 开始位置
            int fromIndex = i * batchsize;
            // 结束位置
            int toIndex = (i+1) * batchsize < size ? ( i+1 ) * batchsize : size ;
            List<Object>  entityListson=entityList.subList(fromIndex,toIndex);
            StringBuffer sql=new StringBuffer();
            sql.append("INSERT INTO"+dbtable+" (*)");
            StringBuffer paramstr=new StringBuffer();
            for (int n = 0; n <entityListson.size() ; n++) {
                paramstr.append("(");
                for (int m = 0; m <fields.length ; m++) {
                    if(m!=fields.length-1){
                        paramstr.append(ReflectUtil.getFieldValue(entityListson.get(n), fields[m].getName())+",");
                    }else{
                        paramstr.append(ReflectUtil.getFieldValue(entityListson.get(n), fields[m].getName()));
                    }
                }
                if(n!=entityListson.size() -1){
                    paramstr.append("),");
                }else{
                    paramstr.append(")");
                }
            }
            sql.append(paramstr);
            ClickHouseConnection connection = getConn();
            try {
                Statement statement = connection.createStatement();
                boolean results = statement.execute(sql.toString());
                return results;
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }
        }
         return true;
    }

    /**
     * 批量插入操作
     * @param dbtable 插入的表
     * @param entityList ignore
     * @return ignore
     */
    public boolean saveBatch(String dbtable, List<Object> entityList) {
       return saveBatch(dbtable,entityList,batchSize);
    }


}
